﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Diagnostics;
using System.Data.OleDb;
using System.Data;

namespace Batbaille.TAC
{
	/// <summary>
	/// Contains several sheets
	/// </summary>
	public class SpreadSheet : IEnumerator
	{
	#region Attributs & Properties

		/// <summary>
		/// Lists of sheets
		/// </summary>
		private List<Sheet> _listSheet;
		internal List<Sheet> ListSheet
		{
			get { return _listSheet; }
			set { _listSheet = value; }
		}

		/// <summary>
		/// Refers to file's path
		/// </summary>
		private string _strFilePath;
		public string FilePath
		{
			get { return _strFilePath; }
			set { _strFilePath = value; }
		}

		/// <summary>
		/// Refers to import the first line
		/// </summary>
		private string _strHDR;
		public string HDR
		{
			get { return _strHDR; }
			set { _strHDR = value; }
		}

		/// <summary>
		/// Refers if it's a import or export data
		/// </summary>
		private bool _bIsReading;
		internal bool IsReading
		{
			get { return _bIsReading; }
			set { _bIsReading = value; }
		}

		/// <summary>
		/// Refers to info of connexion
		/// </summary>
		private string _strConnectionString;
		public  string ConnectionString
		{
			get 
			{ 
				if( _strConnectionString != "" )
					return _strConnectionString;
				else
					return GetBuildConnectionString(FilePath, HDR, IsReading);
			}
			set { _strConnectionString = value; }
		}
				
		/// <summary>
		/// Number of Sheet in the SpreadSheet
		/// </summary>
		public int CountSheet
		{
			get
			{
				return ListSheet.Count;
			}
		}

	#endregion
	#region Constructors
	
		/// <summary>
		/// Default Constructor
		/// </summary>
		private SpreadSheet()
		{
			_strFilePath = "";
			_strHDR = "";
			_strConnectionString = "";
			_listSheet = new List<Sheet>();
			_bIsReading = true;
		}

		/// <summary>
		/// Consctructor with the infos to build the ConnectionString
		/// </summary>
		/// <param name="strFilePath">File's path </param>
		/// <param name="bWithoutFirstLine">No import the first line</param>
		public SpreadSheet(string strFilePath, bool bWithoutFirstLine) : this()
		{
			this.FilePath = strFilePath;

			HDR = "No"; // 1er ligne  importée (elle contient ds données ou des donnée)
			if (bWithoutFirstLine)
				HDR = "Yes"; // 1er ligne non importée (contient les entêtes de colonne ou des donnée) 

			FilePath = strFilePath;		
		}

		/// <summary>
		/// Consctructor with the ConnectionString
		/// </summary>
		/// /// <param name="strConnectionString"> ConnectionString </param>
		public SpreadSheet(string strConnectionString): this()
		{			
			this.ConnectionString = strConnectionString;
		}

	#endregion
	#region Indexers

		/// <summary>
		/// Returns the sheet of the index
		/// </summary>
		/// <param name="nSheetIndex">The index of the sheet</param>
		/// <returns>The sheet of the index</returns>
		public Sheet this[int nSheetIndex]
		{
			get
			{				
				return GetSheet(nSheetIndex);
			}			
		}

		// Ensemble d'attribut et methodes pour être utiliser dans un boucle foreach
		// Group of attibuts and methods 
		private int _nIndex;
		public IEnumerator GetEnumerator() { _nIndex = -1; return this; }
		public bool MoveNext() { return ++_nIndex < ListSheet.Count; }
		public void Reset() { _nIndex = -1; }
		public object Current
		{
			get { return ListSheet[_nIndex]; }
		}

		/// <summary>
		/// Returns the string of indexes
		/// </summary>
		/// <param name="nSheetIndex">Sheet index</param>
		/// <param name="nLineIndex">Line index</param>
		/// <param name="nColumnIndex">Column index</param>
		/// <returns>The string value of indexes</returns>
		public string this[int nSheetIndex, int nLineIndex, int nColumnIndex]
		{
			get
			{
				return GetValue(nSheetIndex, nLineIndex, nColumnIndex);
			}
			set 
			{
				SetValue(nSheetIndex, nLineIndex, nColumnIndex, value);
			}
		}

		

	#endregion
	#region Methods

		/// <summary>
		/// Sheet having the name
		/// </summary>
		/// <param name="strValue">Name of the sheet </param>
		/// <returns>The sheet having the name</returns>
		public int GetSheetIndex( string strName )
		{
			for( int s=0; s<ListSheet.Count; s++ )
			{
				if( ListSheet[s] != null )
				{
					if( ListSheet[s].Name == strName )
						return s;
				}
			}

			return -1;
		}

		/// <summary>
		/// Sheet having the index
		/// </summary>
		/// <param name="nSheetIndex">Index in the list of sheet</param>
		/// <returns>The sheet having the index in the list of sheet </returns>
		public string GetSheetName(int nSheetIndex)
		{
			if (!IsValidIndSheet(nSheetIndex) || ListSheet[nSheetIndex] == null)
				return "";

			return ListSheet[nSheetIndex].Name;
		}

		/// <summary>
		/// Value of the sheet having the name and at the position
		/// </summary>
		/// <param name="strSheetName">Name of the sheet</param>
		/// <param name="strSheetName">Index of line</param>
		/// <param name="strSheetName">Index of column</param>
		/// <returns>The value of the sheet having the name and at the position</returns>
		public string GetValue(string strSheetName, int nLineIndex, int nColumnIndex)
		{
			int nSheetIndex = GetSheetIndex( strSheetName );

			return GetValue(nSheetIndex, nLineIndex, nColumnIndex);			
		}

		/// <summary>
		/// Value of the sheet having the index and at the position
		/// </summary>
		/// <param name="strSheetName">Index of sheet</param>
		/// <param name="strSheetName">Index of line</param>
		/// <param name="strSheetName">Index of column</param>
		/// <returns>The value of the sheet having the index and at the position</returns>
		public string GetValue(int nSheetIndex, int nLineIndex, int nColumnIndex)
		{
			if (!IsValidIndSheet(nSheetIndex) || ListSheet[nSheetIndex] == null)
				return "";

			return ListSheet[nSheetIndex][nLineIndex, nColumnIndex];
		}

		/// <summary>
		/// Update tha value of the sheet having the index and at the position
		/// </summary>
		/// <param name="strSheetName">Index of sheet</param>
		/// <param name="strSheetName">Index of line</param>
		/// <param name="strSheetName">Index of column</param>
		/// <returns>Update ok or not</returns>
		public bool SetValue(int nSheetIndex, int nLineIndex, int nColumnIndex, string strValue)
		{
			if (!IsValidIndSheet(nSheetIndex) || ListSheet[nSheetIndex] == null)
				return false;

			return ListSheet[nSheetIndex].SetValue(nLineIndex, nColumnIndex, strValue);
		}

		/// <summary>
		/// Load the values of all sheet from the file in the speadSheet
		/// </summary>
		public void ReadAll()
		{
			IsReading = true;
			AdoNet_Excel.ReadAll( ConnectionString, this);
		}

		/// <summary>
		/// Save the values of all sheet from the speadSheet in the file 
		/// </summary>
		public void WriteAll()
		{	
			IsReading = false;
			AdoNet_Excel.WriteAll(ConnectionString, this);
		}

		/// <summary>
		/// Save the values of sheet having the name from the speadSheet in the file 
		/// </summary>
		public void WriteSheet(string strSheetName)
		{			
			IsReading = false;
			Sheet oSheet = GetSheet(strSheetName);
			if( oSheet == null )
				return;

			AdoNet_Excel.WriteSheet(ConnectionString, oSheet);
		}

		/// <summary>
		/// Save the values of sheet having the index from the speadSheet in the file 
		/// </summary>
		/// <param name="nSheetIndex">Index of sheet</param>
		public void WriteSheet(int nSheetIndex)
		{
			IsReading = false;
			Sheet oSheet = GetSheet(nSheetIndex);
			if (oSheet == null)
				return;

			AdoNet_Excel.WriteSheet(ConnectionString, oSheet);
		}
		
		/// <summary>
		/// Number of line in the sheet
		/// </summary>
		/// <param name="nSheetIndex">Index of sheet</param>
		/// <returns>Number of line in the sheet</returns>
		public int GetCountLine( int nSheetIndex )
		{			
			Sheet oSheet = GetSheet(nSheetIndex);
			if (oSheet == null)
				return 0;

			return oSheet.CountLine;		
		}

		/// <summary>
		/// Number of column in the sheet
		/// </summary>
		/// <param name="nSheetIndex">Index of sheet</param>
		/// <returns>Number of column in the sheet</returns>
		public int GetCountColumn( int nSheetIndex )
		{
			Sheet oSheet = GetSheet(nSheetIndex);
			if (oSheet == null)
				return 0;

			return oSheet.CountColumn;
		}

		/// <summary>
		/// Total number of values in the sheet
		/// </summary>
		/// <param name="nSheetIndex">Index of sheet</param>
		/// <returns>Total number of values in the sheet</returns>
		public int GetCountValue( int nSheetIndex )
		{
			Sheet oSheet = GetSheet(nSheetIndex);
			if (oSheet == null)
				return 0;

			return GetCountLine(nSheetIndex) * GetCountColumn(nSheetIndex);
		}		

	#endregion
	#region Methods - Tools

		/// <summary>
		/// Index of the sheet is valid
		/// </summary>
		/// <param name="nSheetIndex">Index of sheet</param>
		/// <returns>Index of the sheet is valid</returns>
		private bool IsValidIndSheet( int nSheetIndex )
		{
			if (nSheetIndex < 0 || nSheetIndex >= ListSheet.Count )
				return false;

			return true;
		}

		/// <summary>
		/// Sheet of the index
		/// </summary>
		/// <param name="nSheetIndex">Index of sheet</param>
		/// <returns>Sheet of the index</returns>
		private Sheet GetSheet(int nSheetIndex)
		{
			if (!IsValidIndSheet(nSheetIndex))
				return null;

			return ListSheet[nSheetIndex];
		}

		/// <summary>
		/// Sheet of the name
		/// </summary>
		/// <param name="strSheetName">Name of sheet</param>
		/// <returns>Sheet of the name</returns>
		private Sheet GetSheet(string strSheetName)
		{
			int nSheetIndex = GetSheetIndex(strSheetName);
			return GetSheet(nSheetIndex);
		}

		/// <summary>
		/// Build the connection string
		/// </summary>
		/// <param name="strFilePath">File' path</param>
		/// <param name="strHDR">No import the first line</param>
		/// <param name="bRead">Read or write</param>
		/// <returns>The connection string</returns>
		private static string GetBuildConnectionString(string strFilePath, string strHDR="No", bool bRead=true )
		{			
			var strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + strFilePath;
			
			if( bRead )
				strConnectionString += @"; Extended Properties =""Excel 12.0;HDR=" + strHDR + @";IMEX=1"";";
			else
				strConnectionString += @"; Extended Properties =""Excel 12.0"";";
			
			return strConnectionString;
		}

	#endregion
	}

	/// <summary>
	/// Contains the values of the sheet
	/// </summary>
	public class Sheet : IEnumerator
	{
	#region Attributs & Properties

		/// <summary>
		/// Name of the Sheet
		/// </summary>
		private string _strName;
		public string Name
		{
			get { return _strName; }
			set { _strName = value; }
		}

		/// <summary>
		/// List of the value of hearder columns
		/// </summary>
		private List<string> _ListHeader;
		public List<string> ListHeader
		{
			get { return _ListHeader; }
			set { _ListHeader = value; }
		}
		
		/// <summary>
		/// Double List for the values 
		/// </summary>
		/// <remarks>
		/// first dimension : line
		/// second dimension : column
		/// </remarks>
		private List<List<string>> _listLigne;
		public List<List<string>> ListLine
		{
			get { return _listLigne; }
			set { _listLigne = value; }
		}

		/// <summary>
		/// List of the index of key. Indexes of the header's list are the keys of the table
		/// </summary>
		private List<int> _ListIndKey;
		public List<int> ListIndKey
		{
			get { return _ListIndKey; }
			set { _ListIndKey = value; }
		}

		/// <summary>
		/// Number of Line in the sheet
		/// </summary>
		public int CountLine
		{
			get
			{
				return ListLine.Count;
			}
		}

		/// <summary>
		/// Number of Column in the sheet
		/// </summary>
		public int CountColumn
		{
			get
			{
				return ListHeader.Count;
			}
		}

		/// <summary>
		/// Number of values in the sheet
		/// </summary>
		public int CountValue
		{
			get
			{
				return CountLine * CountColumn;
			}
		}

	#endregion
	#region Constructors
		
		/// <summary>
		/// Default Constructor
		/// </summary>
		public Sheet()
		{
			Clear();
		}

		/// <summary>
		/// Consctructor with the name of the sheet
		/// </summary>
		/// <param name="strName">name of the new sheet</param>
		public Sheet(string strName) 
			: this()
		{
			Name = strName;
		}

		/// <summary>
		/// Consctructor with the name, the number of line and the number of column of the sheet
		/// </summary>
		/// <param name="strName">Name of the new Sheet</param>
		/// <param name="nNbLine">number of Line in the new Sheet</param>
		/// <param name="nNbColumn">number of Column in the new Sheet</param>
		public Sheet(string strName, int nNbLine, int nNbColumn)
			: this(strName)
		{
			ListHeader	= new List<string>(nNbColumn);
			ListLine = new List<List<string>>();
			List<string> ListValue;
			for( int l=0; l<nNbLine; l ++)
			{
				ListValue = new List<string>(nNbColumn);
				ListLine.Add(ListValue);
			}

			ListIndKey	= new List<int>(nNbColumn);
		}		

	#endregion
	#region Indexers

		/// <summary>
		/// Returns the line of values of the index
		/// </summary>
		/// <param name="n">The index</param>
		/// <returns>The line of values of the indexx</returns>
		public List<string> this[int nLineIndex]
		{
			get
			{
				if( !IsValidIndLine(nLineIndex) )
					return new List<string>();

				return ListLine[nLineIndex];
			}
			set
			{
				if (!IsValidIndLine(nLineIndex) )
					return;

				ListLine[nLineIndex] = value;
			}
		}

		/// <summary>
		/// Returns the value of the indexes
		/// </summary>
		/// <param name="nLineIndex">The index of line</param>
		/// <param name="nColumnIndex">The index of column</param>
		/// <returns>The value of the indexes</returns>
		public string this[int nLineIndex,int nColumnIndex]
		{
			get
			{
				return GetValue( nLineIndex, nColumnIndex);
			}
			set 
			{
				SetValue(nLineIndex, nColumnIndex, value);
			}
		}

		// Ensemble d'attribut et methodes pour être utiliser dans un boucle foreach
		// Group of attibuts and methods 
		private int _nIndex;
		public IEnumerator GetEnumerator() { _nIndex = -1; return this; }
		public bool MoveNext() { return ++_nIndex < ListLine.Count; }
		public void Reset() { _nIndex = -1; }
		public object Current
		{
			get { return ListLine[_nIndex]; }
		}

	#endregion
	#region Methods

		/// <summary>
		/// Value of the indexes
		/// </summary>
		/// <param name="nLineIndex">Index of line</param>
		/// <param name="nColumnIndex">Index of column</param>
		/// <returns>Value of the indexes</returns>
		public string GetValue( int nLineIndex, int nColumnIndex)
		{
			if( !IsValidIndex( nLineIndex, nColumnIndex ) )
					return "";

			return ListLine[nLineIndex][nColumnIndex];
		}

		/// <summary>
		/// Update the value of the indexes
		/// </summary>
		/// <param name="nLineIndex">Index of line</param>
		/// <param name="nColumnIndex">Index of column</param>
		/// <param name="strValue">New value</param>
		/// <returns>Update ok or not</returns>
		public bool SetValue(int nLineIndex, int nColumnIndex, string strValue)
		{
			if( !IsValidIndex(nLineIndex, nColumnIndex) )
				return false;

			ListLine[nLineIndex][nColumnIndex] = strValue;
			return true;
		}

		/// <summary>
		/// Header's name of the index
		/// </summary>		
		/// <param name="nColumnIndex">Index of column</param>
		/// <returns>Header's name of the index</returns>
		public string GetHeader( int nColumnIndex )
		{
			if( !IsValidIndColumn( nColumnIndex ) )
				return "";

			return ListHeader[nColumnIndex];
		}

		/// <summary>
		/// Update the header's name of the index
		/// </summary>		
		/// <param name="nColumnIndex">Index of column</param>
		/// <param name="strValue">New value of the header</param>
		/// <returns>Update ok or not</returns>
		public bool SetHeader(int nColumnIndex, string strValue)
		{
			if (!IsValidIndColumn(nColumnIndex))
				return false;

			ListHeader[nColumnIndex] = strValue;
			return true;
		}

		/// <summary>
		/// Clear all infos the sheet
		/// </summary>
		public void Clear()
		{
			Name = "";
			ListHeader = new List<string>();
			ListLine = new List<List<string>>();
			ListIndKey = new List<int>();
		}	

		/// <summary>
		/// Clear all value of the lines (but not the header's value)
		/// </summary>
		public void ClearLine()
		{
			ListLine = new List<List<string>>();
		}

	#endregion
	#region Methods - Tools

		/// <summary>
		/// The indexes are valid
		/// </summary>
		/// <param name="nLineIndex">Index of line</param>
		/// <param name="nColumnIndex">Index of column</param>
		/// <returns>The indexes are valid</returns>
		private bool IsValidIndex( int nLineIndex, int nColumnIndex)
		{
			return IsValidIndLine( nLineIndex ) && IsValidIndLine( nColumnIndex );
		}

		/// <summary>
		/// The index of line is valid
		/// </summary>
		/// <param name="nLineIndex">Index of line</param>
		/// <returns>The index of line is valid</returns>
		private bool IsValidIndLine( int nLineIndex )
		{
			if( nLineIndex < 0 || nLineIndex >= CountLine)
				return false;

			return true;
		}

		/// <summary>
		/// The index of column is valid
		/// </summary>
		/// <param name="nColumnIndex">Index of column</param>
		/// <returns>The index of column is valid</returns>		
		private bool IsValidIndColumn( int nColumnIndex )
		{
			if( nColumnIndex < 0 && nColumnIndex >= CountColumn)
				return false;

			return true;
		}

	#endregion
	}

	internal class AdoNet_Excel
	{
		/// <summary>
		/// Load the values of file in the spreadsheet
		/// </summary>
		/// <param name="ConnectionString">Connection infos</param>
		/// <param name="oSpeadSheet">Spreadsheet object</param>
		internal static void ReadAll(string ConnectionString, SpreadSheet oSpeadSheet)
		{
			OleDbConnection Connection = null;
			try
			{

				using (Connection = new OleDbConnection(ConnectionString))
				{
					// Ouverture de la connexion
					Connection.Open();

					// Récupération de la structure du fichier Ecel
					var dataTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
					if (dataTable == null)
						return;

					// Récupération des Feuilles du fichier Excel
					string[] excelSheets = new String[dataTable.Rows.Count];
					int i = 0;
					foreach (DataRow row in dataTable.Rows)
					{
						excelSheets[i] = row["TABLE_NAME"].ToString();
						i++;
					}
					if (excelSheets.Length <= 0)
						return;

					// Initialisation du nombre de Feuille dans le classeur
					oSpeadSheet.ListSheet = new List<Sheet>();

					// Parcours des feuilles du fichier Excel                    
					Sheet oSheet;
					List<string> ListCase;
					foreach (string strSheetName in excelSheets)
					{
						// Execution de la commande récuppérant toutes les infos de la feuille Excel
						string cmdText = "SELECT * FROM [" + strSheetName + "]";
						OleDbCommand command = new OleDbCommand(cmdText, Connection);
						OleDbDataReader reader = command.ExecuteReader();
						if (reader == null || !reader.HasRows) continue;

						oSheet = new Sheet(strSheetName);
						bool bFirst = true;
						// Parcours des lignes de la feuille Excel                        
						while (reader.Read())
						{
							ListCase = new List<string>();

							// Parcours des colonnes de la feuille Excel
							bool bIsEmptyLine = true; // Indique si la ligne est totalement vide
							for (int c = 0; c < reader.FieldCount; c++)
							{
								string strCase = reader[c].ToString();

								// Enregistrement de l'info de la case sur la ligne
								ListCase.Add(strCase);
								if (strCase != "")
									bIsEmptyLine = false;
							}

							if( bFirst )
							{
								oSheet.ListHeader = ListCase;
								bFirst = false;

							}// Enregistrement de l'info de la ligne sur la feuille
							else if( ListCase.Count > 0 && !bIsEmptyLine)
							{
								oSheet.ListLine.Add(ListCase);
							}

						}
						// Enregistrement de l'info de l'ensemble de la feuille sur le classeur
						if (oSheet.CountLine > 0)
						{
							oSpeadSheet.ListSheet.Add(oSheet);
						}

					}
				}
			}
			catch (Exception e)
			{
				Batbaille.TAC.Message.Exception(e);
			}
			finally
			{
				if (Connection != null) Connection.Close();
			}
		}

		/// <summary>
		/// Save the values of spreadsheet in the file
		/// </summary>
		/// <param name="ConnectionString">Connection infos</param>
		/// <param name="oSpeadSheet">Spreadsheet object</param>
		public static void WriteAll(string strConnectionString, SpreadSheet oSpeadSheet)
		{
			foreach (Sheet oSheet in oSpeadSheet.ListSheet)
			{
				foreach (List<string> oListValue in oSheet.ListLine)
				{
					AdoNet_Excel.InsertOrUpdateLine_AdoNet_Excel(strConnectionString, oSheet.Name, oListValue, oSheet.ListHeader, oSheet.ListIndKey);
				}
			}
		}

		/// <summary>
		/// Save the values of sheet in the file
		/// </summary>
		/// <param name="ConnectionString">Connection infos</param>
		/// <param name="oSheet">Sheet object</param>
		public static void WriteSheet(string strConnectionString, Sheet oSheet)
		{			
			foreach (List<string> oListValue in oSheet.ListLine)
			{
				AdoNet_Excel.InsertOrUpdateLine_AdoNet_Excel(strConnectionString, oSheet.Name, oListValue, oSheet.ListHeader, oSheet.ListIndKey);
			}
		}

		/// <summary>
		/// Update or insert a new line of values from sheet in the file
		/// </summary>
		/// <param name="strConnectionString">Connection infos</param>
		/// <param name="strNameSheet">Name of the sheet</param>
		/// <param name="ListValue">List of values</param>
		/// <param name="ListHeader">List of headers</param>
		/// <param name="ListIndexKey">List of index keys</param>
		internal static void InsertOrUpdateLine_AdoNet_Excel(string strConnectionString, string strNameSheet, List<string> ListValue, List<string> ListHeader, List<int> ListIndexKey)
		{
			int nlineModfif = UpdateLine_AdoNet_Excel(strConnectionString, strNameSheet, ListValue, ListHeader, ListIndexKey);

			if (nlineModfif == 0)
			{
				InsertLine_AdoNet_Excel(strConnectionString, strNameSheet, ListValue, ListHeader);
			}
		}

		/// <summary>
		/// Insert a new line of values from sheet in the file
		/// </summary>
		/// <param name="strConnectionString">Connection infos</param>
		/// <param name="strNameSheet">Name of the sheet</param>
		/// <param name="ListValue">List of values</param>
		/// <param name="ListHeader">List of headers</param>
		/// <param name="ListIndexKey">List of index keys</param>
		/// <returns></returns>
		internal static int InsertLine_AdoNet_Excel(string strConnectionString, string strNameSheet, List<string> ListValue, List<string> ListHeader = null)
		{
			int nLineModif = 0;
			OleDbConnection oConnection = null;

			try
			{
				using (oConnection = new OleDbConnection(strConnectionString))
				{
					oConnection.Open();

					// Les colonnes
					string strListColumn = "";
					if (ListHeader != null && ListHeader.Count >= 1)
					{
						strListColumn = ListHeader[0];

						for (var i = 1; i < ListHeader.Count; i++)
						{
							strListColumn += String.Format(",[{0}]", ListHeader[i].ToString());
						}
					}

					// Les valeurs
					string strListValue = "";
					if (ListValue.Count >= 1)
						strListValue = String.Format("'{0}'", ListValue[0]);

					for (var i = 1; i < ListValue.Count; i++)
					{
						strListValue += String.Format(", '{0}'", ListValue[i]);
					}

					string strCmdText = "";

					if (strNameSheet != "" && strListValue != "" && strListColumn == "")
					{
						strCmdText = "INSERT INTO [" + strNameSheet + "] VALUES (" + strListValue + ")";
					}
					else if (strNameSheet != "" && strListValue != "" && strListColumn != "")
					{
						strCmdText = "INSERT INTO [" + strNameSheet + "] (" + strListColumn + ") VALUES (" + strListValue + ")";
					}

					if (strCmdText != "")
					{
						using (OleDbCommand oCommand = new OleDbCommand(strCmdText, oConnection))
						{
							nLineModif = oCommand.ExecuteNonQuery();
						}
					}
				}
			}
			catch (Exception e)
			{
				Batbaille.TAC.Message.Exception(e);		
			}
			finally
			{
				if (oConnection != null) oConnection.Close();
			}

			return nLineModif;
		}

		/// <summary>
		/// Update a line of values from sheet in the file
		/// </summary>
		/// <param name="strConnectionString">Connection infos</param>
		/// <param name="strNameSheet">Name of the sheet</param>
		/// <param name="ListValue">List of values</param>
		/// <param name="ListHeader">List of headers</param>
		/// <param name="ListIndexKey">List of index keys</param>
		/// <returns></returns>
		internal static int UpdateLine_AdoNet_Excel(string strConnectionString, string strNameSheet, List<string> ListValue, List<string> ListHeader, List<int> ListIndexKey)
		{			
			int nLineModif = 0;
			OleDbConnection oConnection = null;
			
			try
			{
				using (oConnection = new OleDbConnection(strConnectionString))
				{

					oConnection.Open();

					if (ListValue.Count != ListHeader.Count || ListValue.Count <= 0 || ListIndexKey.Count <= 0)
						return 0;

					// Les colonnes et leur valeurs
					string strListValue = "";
					if (ListHeader.Count >= 1 && ListValue.Count >= 1)
					{
						strListValue = String.Format("[{0}] = '{1}'", ListHeader[0], ListValue[0]);
					}
					
					for (var i = 1; i < ListHeader.Count; i++)
					{
						strListValue += String.Format(",[{0}] = '{1}'", ListHeader[i], ListValue[i]);
					}
					
					// Les colonnes et leur valeurs
					string strListKey = "";
					if (ListIndexKey.Count >= 1)
					{
						int nIndexKey = ListIndexKey[0];
						strListKey = String.Format("[{0}] = '{1}'", ListHeader[nIndexKey], ListValue[nIndexKey]);
					}
					
					for (var i = 1; i < ListIndexKey.Count; i++)
					{
						int nIndexKey = ListIndexKey[i];
						strListKey += String.Format("AND [{0}] = '{1}'", ListHeader[nIndexKey], ListValue[nIndexKey]);
					}
					
					if (strNameSheet != "" && strListValue != "" && strListKey != "")
					{
						string strCmdText = "UPDATE [" + strNameSheet + "] SET " + strListValue + " WHERE " + strListKey;						
						using (OleDbCommand oCommand = new OleDbCommand(strCmdText, oConnection))
						{
							nLineModif = oCommand.ExecuteNonQuery();
						}
					}
				}
			}
			catch (Exception e)
			{
				Batbaille.TAC.Message.Exception(e);
			}
			finally
			{
				if (oConnection != null) oConnection.Close();
			}

			return nLineModif;
		}
	}
}
